package ph.com.gs3.formalistics.model.dao;

import java.util.ArrayList;
import java.util.List;

import org.json.JSONException;
import org.json.JSONObject;

import ph.com.gs3.formalistics.global.utilities.logging.FLLogger;
import ph.com.gs3.formalistics.model.tables.OutgoingDocumentsTable;
import ph.com.gs3.formalistics.model.valueobjects.business.document.OutgoingDocument;
import ph.com.gs3.formalistics.view.valueobjects.parsers.DateUtilities;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class OutgoingDocumentsDAO extends SQLiteDataAccessObject {

	public static final String TAG = OutgoingDocumentsDAO.class.getSimpleName();

	private final String[] columns;

	public OutgoingDocumentsDAO(Context context) {
		super(context);

		columns = new OutgoingDocumentsTable().getTableColumns();
	}

	public void saveOutgoingDocument(OutgoingDocument outgoingDocumentAction) {

		String server = outgoingDocumentAction.getServer();
		String documentWebId = outgoingDocumentAction.getDocumentWebId();

		if (isOutgoingDocumentActionExisting(server, documentWebId)) {
			updateOutgoingDocument(outgoingDocumentAction);
		} else {
			String currentDate = DateUtilities.getServerFormattedCurrentDate();
			outgoingDocumentAction.setDateIssued(currentDate);

			ContentValues cv = createCVFromOutgoingDocumentACtion(outgoingDocumentAction);

			long insertId = database.insert(OutgoingDocumentsTable.NAME, null, cv);

			if (insertId <= 0) {
				throw new SQLException("Failed to save outgoing document.");
			}
		}

	}

	public void updateOutgoingDocument(OutgoingDocument outgoingDocumentAction) {

		ContentValues cv = createCVFromOutgoingDocumentACtion(outgoingDocumentAction);

		String server = outgoingDocumentAction.getServer();
		String documentWebId = outgoingDocumentAction.getDocumentWebId();

		String whereClause = OutgoingDocumentsTable.COL_SERVER + "=? AND "
		        + OutgoingDocumentsTable.COL_DOCUMENT_WEB_ID + "=?";
		String[] whereArgs = { server, documentWebId };

		long affectedRows = database
		        .update(OutgoingDocumentsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows <= 0) {
			throw new SQLException("Failed to save outgoing document.");
		}

		// the affected rows should only be one, show a warning if it's more than that.
		if (affectedRows > 1) {
			FLLogger.w(
			        TAG,
			        "Warning, there are "
			                + affectedRows
			                + " updated outgoing documents after updating the outgoing document with document id = "
			                + documentWebId + " from " + server);
		}

	}

	public boolean isOutgoingDocumentActionExisting(String server, String documentWebId) {

		String whereClause = OutgoingDocumentsTable.COL_SERVER + "=? AND "
		        + OutgoingDocumentsTable.COL_DOCUMENT_WEB_ID + "=?";
		String[] whereArgs = { server, documentWebId };

		String[] columns = { OutgoingDocumentsTable.COL_ID };

		Cursor cursor = database.query(OutgoingDocumentsTable.NAME, columns, whereClause,
		        whereArgs, null, null, null);

		cursor.moveToFirst();
		boolean existing = !cursor.isAfterLast();
		cursor.close();

		return existing;

	}

	public void removeOutgoingDocument(String server, String documentWebId) {

		String whereClause = OutgoingDocumentsTable.COL_SERVER + "=? AND "
		        + OutgoingDocumentsTable.COL_DOCUMENT_WEB_ID + "=?";
		String[] whereArgs = { server, documentWebId };

		long affectedRows = database.delete(OutgoingDocumentsTable.NAME, whereClause, whereArgs);

		if (affectedRows <= 0) {
			throw new SQLException("Failed to remove outgoing document.");
		}

		// the affected rows should only be one, show a warning if it's more than that.
		if (affectedRows > 1) {
			FLLogger.w(
			        TAG,
			        "Warning, there are "
			                + affectedRows
			                + " deleted outgoing documents after deleting the outgoing document with document web id = "
			                + documentWebId + " from " + server);
		}

	}

	public OutgoingDocument getOutgoingDocument(String documentDbId) {

		String selection = OutgoingDocumentsTable.COL_ID + "=?";
		String[] selectionArgs = { documentDbId };

		Cursor cursor = database.query(OutgoingDocumentsTable.NAME, columns, selection,
		        selectionArgs, null, null, null);
		cursor.moveToFirst();

		OutgoingDocument document = cursorToOutgoingDocumentAction(cursor);
		cursor.close();

		return document;

	}

	public List<OutgoingDocument> getAllOutgoingDocument(String currentUserDbId) {

		// TODO: make a more readable version of the query builder

		// build the query
		// String query = "SELECT " + OutgoingDocumentsTable.NAME + ".*, " +
		// DocumentsTable.NAME + "."
		// + DocumentsTable.COL_FORM_WEB_ID + " FROM " + OutgoingDocumentsTable.NAME
		// + " LEFT JOIN " + DocumentsTable.NAME + " ON " + OutgoingDocumentsTable.NAME +
		// "."
		// + OutgoingDocumentsTable.COL_DOCUMENT_ID + " = " + DocumentsTable.NAME + "."
		// + DocumentsTable.COL_ID + " WHERE " +
		// OutgoingDocumentsTable.COL_ISSUED_BY_USER_ID
		// + "=?";
		//
		// String[] queryArgs = { currentUserDbId };
		//
		// Cursor cursor = database.rawQuery(query, queryArgs);

		String selection = OutgoingDocumentsTable.COL_ISSUED_BY_USER_ID + "=?";
		String[] selectionArgs = { currentUserDbId };

		Cursor cursor = database.query(OutgoingDocumentsTable.NAME, columns, selection,
		        selectionArgs, null, null, null);
		cursor.moveToFirst();

		List<OutgoingDocument> outgoingDocumentActions = new ArrayList<>();

		while (!cursor.isAfterLast()) {
			outgoingDocumentActions.add(cursorToOutgoingDocumentAction(cursor));
			cursor.moveToNext();
		}

		cursor.close();

		return outgoingDocumentActions;

	}

	private ContentValues createCVFromOutgoingDocumentACtion(OutgoingDocument outgoingDocumentAction) {

		JSONObject fieldValues = outgoingDocumentAction.getFieldValues();
		if (fieldValues == null) {
			throw new IllegalArgumentException(
			        "Unable to create content values from outgoing document action, the field values must not be null.");
		}

		ContentValues cv = new ContentValues();
		cv.put(OutgoingDocumentsTable.COL_SERVER, outgoingDocumentAction.getServer());
		cv.put(OutgoingDocumentsTable.COL_DOCUMENT_FORM_WEB_ID,
		        outgoingDocumentAction.getDocumentFormWebId());
		cv.put(OutgoingDocumentsTable.COL_DOCUMENT_FORM_NAME,
		        outgoingDocumentAction.getDocumentFormName());
		cv.put(OutgoingDocumentsTable.COL_DOCUMENT_WEB_ID,
		        outgoingDocumentAction.getDocumentWebId());

		cv.put(OutgoingDocumentsTable.COL_ISSUED_BY_USER_ID,
		        outgoingDocumentAction.getIssuedByUserId());

		if (outgoingDocumentAction.getDateIssued() != null) {
			cv.put(OutgoingDocumentsTable.COL_DATE_ISSUED, outgoingDocumentAction.getDateIssued());
		}

		cv.put(OutgoingDocumentsTable.COL_FIELD_VALUES_JSON, fieldValues.toString());
		cv.put(OutgoingDocumentsTable.COL_ACTION, outgoingDocumentAction.getAction());
		cv.put(OutgoingDocumentsTable.COL_ERROR_MESSAGE, outgoingDocumentAction.getErrorMessage());

		return cv;

	}

	private OutgoingDocument cursorToOutgoingDocumentAction(Cursor cursor) {

		// initialize indices
		// @formatter:off
		int idIndex 					= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_ID);
		int serverIndex					= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_SERVER);		
		int documentDbIdIndex 			= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_DOCUMENT_WEB_ID);
		int documentFormIdIndex 		= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_DOCUMENT_FORM_WEB_ID);
		int documentFormNameIndex		= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_DOCUMENT_FORM_NAME);
		
		int issuedByUserIdIndex 		= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_ISSUED_BY_USER_ID);
		int dateIssuedIndex 			= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_DATE_ISSUED);
		int fieldValuesIndex			= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_FIELD_VALUES_JSON);
		int actionIndex 				= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_ACTION);
		int errorMessageIndex 			= cursor.getColumnIndexOrThrow(OutgoingDocumentsTable.COL_ERROR_MESSAGE);
		
		String id				 		= cursor.getString(idIndex);
		String server					= cursor.getString(serverIndex);		
		String documentWebId 			= cursor.getString(documentDbIdIndex);
		String documentFormId 			= cursor.getString(documentFormIdIndex);
		String documentFormName			= cursor.getString(documentFormNameIndex);

		String issuedByUserId 			= cursor.getString(issuedByUserIdIndex);
		String dateIssued 				= cursor.getString(dateIssuedIndex);
		String fieldValuesRaw			= cursor.getString(fieldValuesIndex);
		String action 					= cursor.getString(actionIndex);
		String errorMessage 			= cursor.getString(errorMessageIndex);
		// @formatter:on

		try {
			JSONObject fieldValues = new JSONObject(fieldValuesRaw);
			OutgoingDocument outgoingDocument = new OutgoingDocument();

			outgoingDocument.setId(id);
			outgoingDocument.setServer(server);
			outgoingDocument.setDocumentWebId(documentWebId);
			outgoingDocument.setDocumentFormWebId(documentFormId);
			outgoingDocument.setDocumentFormName(documentFormName);

			outgoingDocument.setIssuedByUserId(issuedByUserId);
			outgoingDocument.setDateIssued(dateIssued);
			outgoingDocument.setFieldValues(fieldValues);
			outgoingDocument.setAction(action);
			outgoingDocument.setErrorMessage(errorMessage);

			return outgoingDocument;
		} catch (JSONException e) {
			// this should not happen since the JSON object is already qualified before
			e.printStackTrace();
			return null;
		}

	}

}
